﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using OBUCare.Core.Common;

namespace OBUCare.Core.Common
{
    /// <summary>
    /// NPOI访问Excel的助手类
    /// </summary>
    public class NPOIExcelHelper
    {
        HSSFWorkbook mWorkbook;
        ISheet mSheet;
        string mfileName;

        public NPOIExcelHelper(string fileName)
        {
            this.mfileName = fileName;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                mWorkbook = new HSSFWorkbook(file);
                this.ActiveSheetIndex = 0;
            } 

        }

        public int ActiveSheetIndex 
        {
            get {
                return mWorkbook.ActiveSheetIndex;
            }
            set {
                try
                {
                    mSheet = mWorkbook.GetSheetAt(value);
                    mWorkbook.SetActiveSheet(value);                    
                }
                catch(Exception e) 
                {
                
                }
            }
        }

        /// <summary>
        /// 读取或写入指定单元格
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public string this[int rowIndex, int columnIndex]
        {
            get
            {
                string temp=null;
                var row = mSheet.GetRow(rowIndex);                
                if (row != null)
                { 
                    var cell = row.GetCell(columnIndex);
                    if (cell != null)
                        temp = cell.ToText();
                    }
                return temp;
            }
            set
            {                
                var row = mSheet.GetRow(rowIndex);
                if (row == null)
                    row = mSheet.CreateRow(rowIndex);
                var cell = row.GetCell(columnIndex);
                if (cell == null)
                    cell = row.CreateCell(columnIndex);
                cell.SetCellValue(value);                
            }
        }

        private ICell getCell(int rowIndex,int cellIndex)
        {
            var row = mSheet.GetRow(rowIndex);
            if (row == null)
                row = mSheet.CreateRow(rowIndex);
            var cell = row.GetCell(cellIndex);
            if (cell == null)
                cell = row.CreateCell(cellIndex);

            return cell;
        }

        /// <summary>
        /// 设置行的样式
        /// </summary>
        /// <param name="rowStyle"></param>
        public void SetRowStyle(int rowIndex,ICellStyle rowStyle)
        {
            
            var row = mSheet.GetRow(rowIndex);
            if (row != null)
                row.RowStyle = rowStyle;
        }

        /// <summary>
        /// 设置行的样式
        /// </summary>
        /// <param name="rowStyle"></param>
        public void SetCellStyle(int templateRowIndex, int templateCellIndex, int targetRowIndex, int targetCellIndex)
        {
            var cell = getCell(templateRowIndex, templateCellIndex);
            var style = cell.CellStyle;
            if (style != null)
            {
                var tCell = getCell(targetRowIndex, targetCellIndex);
                tCell.Row.Height = cell.Row.Height;
                tCell.CellStyle = style;
            }
            
        }

        /// <summary>
        /// 设置行的样式
        /// </summary>
        /// <param name="rowStyle"></param>
        public ICellStyle GetRowStyle(int rowIndex)
        {
            ICellStyle rowStyle = null;
            var row = mSheet.GetRow(rowIndex);
            if (row != null&&row.IsFormatted)
                rowStyle= row.RowStyle;

            return rowStyle;
        }

        /// <summary>
        /// 设置单元格公式
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="formula"></param>
        public void SetCellFormula(int rowIndex, int columnIndex,string formula)
        {
            var row = mSheet.GetRow(rowIndex);
            if (row == null)
                row = mSheet.CreateRow(rowIndex);
            var cell = row.GetCell(columnIndex);
            if (cell == null)
                cell = row.CreateCell(columnIndex);
            cell.SetCellFormula(formula);
        }

        /// <summary>
        /// 设置单元值 
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="formula"></param>
        public void SetCellValue(int rowIndex, int columnIndex, double? v)
        {
            var row = mSheet.GetRow(rowIndex);
            if (row == null)
                row = mSheet.CreateRow(rowIndex);
            var cell = row.GetCell(columnIndex);
            if (cell == null)
                cell = row.CreateCell(columnIndex);
            if(v.HasValue)
                cell.SetCellValue(v.Value);            
        }

        /// <summary>
        /// 设置单元值 
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="formula"></param>
        public void SetCellValue(int rowIndex, int columnIndex, int? v)          
        {
            var row = mSheet.GetRow(rowIndex);
            if (row == null)
                row = mSheet.CreateRow(rowIndex);
            var cell = row.GetCell(columnIndex);
            if (cell == null)
                cell = row.CreateCell(columnIndex);
            if (v.HasValue)
                cell.SetCellValue(v.Value);
        }

        /// <summary>
        /// 设置单元值 
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="formula"></param>
        public void SetCellValue(int rowIndex, int columnIndex, decimal? v)
        {
            double? d = (double?)v;
            SetCellValue(rowIndex, columnIndex, d);
        }

        public void ShiftRows(int startRow, int endRow, int n)
        {
            mSheet.ShiftRows(startRow,endRow,n,true,false);
        }

        public int LastRowIndex
        {
            get
            {
                return mSheet.LastRowNum;
            }
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="firstRow"></param>
        /// <param name="lastRow"></param>
        /// <param name="firstCol"></param>
        /// <param name="lastCol"></param>
        public void Merge(int firstRow, int lastRow, int firstCol, int lastCol)
        {
            mSheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        }


        /// <summary>
        /// 将文件保存
        /// </summary>   
        /// <returns></returns>
        public bool Save()
        {
            return this.saveToFile(this.mfileName);
        }

        /// <summary>
        /// 将文件另存
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public bool SaveAs(string fileName)
        {
            return this.saveToFile(fileName);
        }

        /// <summary>
        /// 将Excel写入流中
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public bool SaveAs(Stream stream)
        {
            bool success = false;
            try
            {
                mWorkbook.Write(stream);
                success = true;
            }
            catch(Exception ex)
            {
                
            }
            return success;
        }



        /// <summary>
        /// 将修改后的workbook写入到指文件
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private bool saveToFile(string fileName)
        {
            bool success = false;
            try
            {
                using (FileStream file = new FileStream(fileName, FileMode.Create))
                {
                    mWorkbook.Write(file);
                }
                success = true;
            }
            catch (Exception e)
            { 
                
            }
            return success;
        }

    }
}